var tools = require('../../../libs/tools');
var config = require(tools.rootPath + 'config');
var { connection, pool, model } = require(tools.rootPath + 'libs/model');
const Excel = require('exceljs');
const fs = require('fs');

var controller = require(tools.rootPath + 'libs/controller.js');
class excel extends controller {
    constructor() {
        super();
    }

    index(req, res) {
        res.end('controller: admin/excel/index');
    }

    //https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md#access-worksheets
    async read_excel(req, res) {
        var workbook = new Excel.Workbook();
        await workbook.xlsx.readFile(tools.publicPath + 'xlsxs/szh_article.xlsx');
        var worksheet = workbook.getWorksheet(1);
        console.log('最大行号：', worksheet.rowCount);
        console.log('最大列号：', worksheet.columnCount);

        var firstRow = worksheet.getRow(1).values;
        console.log('第一行：', firstRow);
        var secondRow = worksheet.getRow(2);
        console.log('第二行：', secondRow.getCell(3).text, secondRow.getCell(4).text);

        let data = [];
        worksheet.eachRow({ includeEmpty: true }, function (row, rowNumber) {
            if (rowNumber == 1) return;
            // data.push({
                // no: rowNumber,
                // itemid: row.values[1],
                // title: row.values[3],
                // username: row.values[10],
            // });
			data.push({
                no: rowNumber,
                itemid: row.getCell(1).text,
                title: row.getCell(3).text,
                username: row.getCell(10).text,
            });
        });


        res.render(req.TPL, { data: data });
    }

    async write_excel(req, res) {
        //数据库取出数据
        let data = await model.select("select * from article");
        var workbook = new Excel.Workbook();
        //创建一个新的工作表
        var worksheet = workbook.addWorksheet('文章数据');

        //写入表头
        worksheet.columns = [
            { header: 'itemid', key: 'itemid', width: 30 },
            { header: 'title', key: 'title', width: 30 },
            { header: 'content', key: 'content', width: 30 }
        ];

        //设置列宽
        //worksheet.getColumn('A').width = 20;
        //worksheet.getColumn('B').width = 20;
        //worksheet.getColumn('C').width = 20;


        //worksheet.addRow(['itemid', 'title', 'content']);
        //写入数据
        worksheet.addRows(data);

        let filename = tools.publicPath + 'xlsxs/' + new Date().getTime() + '.xlsx'
        let r = await workbook.xlsx.writeFile(filename);
        console.log(r, filename);

        // res.end('ok');
        //抛出下载
        res.attachment(filename);
        res.end(fs.readFileSync(filename));
    }

}

module.exports = excel;